package com.bupt.dao;

import com.bupt.bean.Map;
import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.data.jpa.repository.JpaSpecificationExecutor;
import org.springframework.data.jpa.repository.Query;
import org.springframework.data.repository.query.Param;

import java.util.List;

public interface MapRepository  extends JpaRepository<Map,String>, JpaSpecificationExecutor<Map> {

    @Query(value="SELECT longitude,latitude FROM map_t",nativeQuery=true)
    List<java.util.Map<String,Object>> findOneLongiAndlati();


    @Query(value="SELECT cs.section_index,cs.start_position,cs.end_position FROM  channel_section_t cs",nativeQuery=true)
    List<java.util.Map<String,Object>> findAreaWarning();

    @Query(value="SELECT c.section_name,c.longitude,c.latitude,c.event_desc,c.creat_time,d.const_desc,c.position FROM (\n"+
            "SELECT cs.section_name,e.longitude,e.latitude,e.event_desc,DATE_FORMAT(e.creat_time,'%Y-%m-%d %H:%i:%s')AS creat_time,e.event_type,e.position FROM  channel_section_t             cs  LEFT JOIN event_t e \n"+
            "ON cs.channel_section_id=e.channel_section_id AND e.status=1  AND cs.section_index=:section_index )c ,(SELECT const_value,const_desc FROM sys_const_t WHERE const_group_name='EVENT_TYPE')d\n"+
            "WHERE c.event_type=d.const_value ORDER BY creat_time DESC \n",nativeQuery=true)
    List<java.util.Map<String,Object>> getAreaToEvent(@Param("section_index") String section_index);


    /**SELECT c.*,dc.channel_index,cs.section_name FROM (
     SELECT ast.threshold,ast.alarm_strategy_id,d.device_name,ast.device_channel_id,ast.channel_section_id,ast.attribute_name_cn AS asattribute_name_cn,dta.device_type_id,dta.attribute_name_cn FROM alarm_strategy_t ast,device_t d,device_type_attribute_check_t dta
     WHERE d.device_id=ast.device_id AND dta.device_type_id=d.device_type_id)c LEFT JOIN  device_channel_t dc ON dc.device_channel_id=c.device_channel_id
     LEFT JOIN  channel_section_t cs ON cs.channel_section_id=c.channel_section_id


     SELECT * FROM device_t

     SELECT * FROM device_channel_t

     SELECT * FROM channel_section_t

     SELECT * FROM alarm_strategy_t
     COMMIT;

     SELECT * FROM device_type_attribute_check_t

     SELECT * FROM sys_const_t WHERE const_group_name='STRATEGY_TYPE'


     SELECT ast.alarm_strategy_id,d.device_name,ast.device_channel_id FROM alarm_strategy_t ast LEFT JOIN device_t d
     ON  d.device_id=ast.device_id LEFT  device_channel_t dc ON dc.device_channel_id=ast.device_channel_id
     * */
}
